package com.hsnn.medstgmini.trading.pur.dao.impl;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.Constants;
import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.exception.MSException;
import com.hsnn.medstgmini.trading.pur.dao.PurOrderInfoDao;
import com.hsnn.medstgmini.trading.pur.model.OrderDetailStatus;
import com.hsnn.medstgmini.trading.pur.model.OrderPackStatus;
import com.hsnn.medstgmini.trading.pur.model.PurAoginfo;
import com.hsnn.medstgmini.trading.pur.model.PurDelOrderInfo;
import com.hsnn.medstgmini.trading.pur.model.PurOrderInfo;
import com.hsnn.medstgmini.trading.pur.model.PurOrderLogisticsOld;
import com.hsnn.medstgmini.trading.pur.model.PurOrderpack;
import com.hsnn.medstgmini.trading.pur.model.form.DispatchInfo;
import com.hsnn.medstgmini.trading.pur.model.form.OrderPackage;
import com.hsnn.medstgmini.trading.pur.model.form.PurOrderInfoForm;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.ParseDate;
import com.hsnn.medstgmini.util.ParseNumber;
import com.hsnn.medstgmini.util.PinYin;

@Repository
public class PurOrderInfoDaoImpl extends BaseHibernateDAO implements PurOrderInfoDao {
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	@Autowired
	private HibernateSqlUtil dao;

	/**
	 * @category 订单明细查询[中心端]
	 * @author 应晓川
	 * @date 2015年7月9日
	 */
	@Override
	public Pagination getOrdersDetailList(Pagination page) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.remarks,a.order_pack_id orderPackId, a.hosp_code hospCode, a.hosp_name hospName,  ");
		sb.append(" a.hosp_ar_fullname hospArFullname, a.count, a.amount, a.is_urgent isUrgent, ");
		sb.append(" DATE_FORMAT(a.submit_datetime, '%Y-%m-%d %T') submitDatetime,a.del_comp_code delCompCode, ");
		sb.append(" a.recp_addr recpAddr, a.recp_userphone recpUserphone, a.recp_user recpUser,a.del_comp_name delCompName,a.order_status orderStatus ");
		sb.append(" FROM pur_orderpack a WHERE  ");
		sb.append("  a.order_status != :orderStatus ");
		
		OrderPackage orderPackage = (OrderPackage) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("orderStatus", OrderPackStatus.finishOrder.getKey());
		
		if (StringUtils.isNotBlank(orderPackage.getHospName())) {
			sb.append(" AND ( a.hosp_name LIKE :hospName OR a.hosp_spel_code LIKE :hospName ) ");
			params.put("hospName", "%" + orderPackage.getHospName() + "%");
		}
		
		if (StringUtils.isNotBlank(orderPackage.getAreaId())) {
			sb.append(" AND a.hosp_area_id LIKE :areaId ");
			params.put("areaId", AreaUtil.escapeAreaId(orderPackage.getAreaId()));
		}
		
		if (StringUtils.isNotBlank(orderPackage.getOrderPackId())) {
			sb.append(" AND a.order_pack_id = :orderPackId ");
			params.put("orderPackId", orderPackage.getOrderPackId());
		}
		
		if (orderPackage.getStartDate() != null) {
			sb.append(" AND DATE_FORMAT(a.submit_datetime, '%Y-%m-%d') >= :startDate ");
			params.put("startDate", DateUtil.date(orderPackage.getStartDate()));
		}
		
		if (orderPackage.getEndDate() != null) {
			sb.append(" AND DATE_FORMAT(a.submit_datetime, '%Y-%m-%d') <= :endDate ");
			params.put("endDate", DateUtil.date(orderPackage.getEndDate()));
		}
		if(StringUtils.isNotBlank(orderPackage.getDelCompName())){
			sb.append(" and (a.del_comp_name like :delCompName or a.del_comp_name_spel like :delCompName)");
			params.put("delCompName", "%"+orderPackage.getDelCompName()+"%");
		}

		if (orderPackage.getOrderStatus() != null) {
			sb.append(" AND a.order_status = :status ");
			params.put("status", orderPackage.getOrderStatus());
		}
		
		return dao.getPageMapList(sb.toString(), params, page);
	}

	/**
	 * @category 采购订单查询[医疗机构]
	 * @author 应晓川
	 * @date 2015年7月13日
	 */
	@SuppressWarnings("rawtypes")
	@Override
	public Pagination getOrderInfoByHosp(Pagination page) {

		String hospId = page.getConditions().get("hospId");
		String orderCode = page.getConditions().get("orderCode");
		String submitStartDatetime = page.getConditions().get(
				"submitStartDatetime");
		String submitEndDatetime = page.getConditions()
				.get("submitEndDatetime");
		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		String compName = page.getConditions().get("compName");
		String prodComp = page.getConditions().get("prodComp");
		String orderStatus = page.getConditions().get("orderStatus");
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT SQL_CALC_FOUND_ROWS poi.order_code AS orderCode,                                     ");
		sql.append("       poi.order_id AS orderId,                                                              ");
		sql.append("       Date_format(poi.join_plan_time, '%Y-%m-%d %H:%i:%S') AS joinPlanTime,                 ");
		sql.append("       poi.pro_price AS proPrice,                                                            ");
		sql.append("       poi.pro_count AS proCount,                                                            ");
		sql.append("       (poi.pro_count * poi.pro_price) AS totalPrice,                                        ");
		sql.append("       sci.comp_name AS compName,                                                            ");
		sql.append("       sci.comp_id AS compId,                                                                ");
		sql.append("       Date_format(poi.submit_datetime, '%Y-%m-%d %H:%i:%S') AS submitDatetime,              ");
		sql.append("       Date_format(pdoi.del_datetime, '%Y-%m-%d %H:%i:%S') AS delDatetime,                   ");
		sql.append("       pdoi.del_count AS delCount,                                                           ");
		sql.append("       (pdoi.del_count*poi.pro_price) AS delTotalPrice,                                      ");
		sql.append("       vd.drug_code AS drugCode,                                                             ");
		sql.append("       vd.drug_name AS drugName,                                                             ");
		sql.append("       vd.form_name AS formName,                                                             ");
		sql.append("       vd.spec_name AS specName,                                                             ");
		sql.append("       vd.factor,                                                                            ");
		sql.append("       vd.unit,                                                                              ");
		sql.append("       vd.material_name AS materialName,                                                     ");
		sql.append("       vd.comp_name AS prodComp ,                                                            ");
		sql.append("       vd.prod_comp_id AS prodCompId ,                                                       ");
		sql.append("       poi.goods_code AS goodsCode,                                                          ");
		sql.append("       poi.pro_price_limit AS proPriceLimit,                                                 ");
		sql.append("       bg.bak_drug_flag AS bakDrugFlag,                                                      ");
		sql.append("       poi.is_longtime_plan AS isLongtimePlan,                                               ");
		sql.append("       poi.order_pack_id AS orderPackId,                                                     ");
		sql.append("       (CASE poi.order_status WHEN 1 THEN '已提交' WHEN 2 THEN '确认配送' WHEN 3 THEN '无法配送' WHEN 4 THEN '已配送' WHEN 5 THEN '已到货' WHEN 6 THEN '未到货' ELSE '其他' END )AS orderStatus,                                                     ");
		sql.append("       shi.hosp_id AS hospId,                                                                ");
		sql.append("       vd.drug_id AS drugId                                                                  ");
		sql.append("     FROM pur_orderinfo poi LEFT JOIN  pur_delorderinfo pdoi ON poi.order_id = pdoi.order_id,");
		sql.append("     std_hospinfo shi,                                                                       ");
		sql.append("     view_drug vd,                                                                           ");
		sql.append("     std_compinfo sci,                                                                       ");
		sql.append("     base_goods bg                                                                           ");
		sql.append("WHERE poi.hosp_id = shi.hosp_id                                                              ");
		sql.append("  AND poi.del_comp_id = sci.comp_id                                                          ");
		sql.append("  AND poi.drug_id = vd.drug_id                                                               ");
		sql.append("  AND poi.goods_code = bg.goods_code                                                         ");
		sql.append("  AND shi.hosp_id = :hospId                                                                  ");

		if (StringUtils.isNotBlank(orderCode)) {
			sql.append(" AND poi.order_code = :orderCode ");
		}
		if (StringUtils.isNotBlank(submitStartDatetime)
				&& StringUtils.isNotBlank(submitEndDatetime)) {
			sql.append(" AND date(poi.submit_datetime) between :submitStartDatetime and :submitEndDatetime ");
		}
		if (StringUtils.isNotBlank(drugCode)) {
			sql.append(" AND vd.drug_code = :drugCode ");
		}
		if (StringUtils.isNotBlank(drugName)) {
			sql.append(" AND (vd.drug_name like :drugName or vd.drug_name_spel like :drugName or vd.drug_name_eng like :drugName )");
		}
		if (StringUtils.isNotBlank(compName)) {
			sql.append(" AND (sci.comp_name like :compName or sci.comp_spel_code like :compName )");
		}
		if (StringUtils.isNotBlank(prodComp)) {
			sql.append(" AND (vd.comp_name like :prodComp or vd.comp_spel_code like :prodComp )");
		}
		if (StringUtils.isNotBlank(orderStatus)) {
			sql.append(" AND poi.order_status = :orderStatus ");
		}
		sql.append(" order by  poi.join_plan_time desc ");
		Query query = sessionFactory.getCurrentSession().createSQLQuery(
				sql.toString());
		Query queryCount = sessionFactory.getCurrentSession().createSQLQuery(
				"SELECT found_rows() AS rowcount");
		query.setParameter("hospId", hospId);
		if (StringUtils.isNotBlank(orderCode)) {
			query.setParameter("orderCode", orderCode);
		}
		if (StringUtils.isNotBlank(submitStartDatetime)
				&& StringUtils.isNotBlank(submitEndDatetime)) {
			query.setParameter("submitStartDatetime", submitStartDatetime);
			query.setParameter("submitEndDatetime", submitEndDatetime);
		}
		if (StringUtils.isNotBlank(drugCode)) {
			query.setParameter("drugCode", drugCode);
		}
		if (StringUtils.isNotBlank(drugName)) {
			query.setParameter("drugName", "%" + drugName + "%");
		}
		if (StringUtils.isNotBlank(compName)) {
			query.setParameter("compName", "%" + compName + "%");
		}
		if (StringUtils.isNotBlank(prodComp)) {
			query.setParameter("prodComp", "%" + prodComp + "%");
		}
		if (StringUtils.isNotBlank(orderStatus)) {
			query.setParameter("orderStatus", orderStatus);
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List list = query.list();
		BigInteger total = (BigInteger) queryCount.uniqueResult();
		// int records = ((Number)countQuery.uniqueResult()).intValue();
		page.setRecords(total.longValue());
		page.setRows(list);
		return page;
	}

	/**
	 * @category 订单明细查询[生产企业端]
	 * @author 应晓川
	 * @date 2015年7月9日
	 */
	@Override
	public Pagination getOrdersDetailListByProComp(Pagination page) {
		String prodCompcode = page.getConditions().get("prodCompcode");
		String drugCode = page.getConditions().get("drugCode");
		String drugName = page.getConditions().get("drugName");
		String hospName = page.getConditions().get("hospName");
		String delCompName = page.getConditions().get("delCompName");
		String hospAreaId = AreaUtil.getAreaStartCode(page.getConditions().get("area1"),page.getConditions().get("area2"),page.getConditions().get("area3"));
		
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.order_code orderCode, a.order_pack_id orderPackId, ");
		sb.append("     a.pro_count proCount, a.del_count delCount, a.pro_price proPrice,  ");
		sb.append("     a.join_plan_time joinPlanTime, a.join_plan_user joinPlanUser,  ");
		sb.append("     a.order_status orderStatus, a.confirm_remark confirmRemark,  ");
		sb.append("     a.goods_code goodsCode, a.area_name areaName, a.pro_count * a.pro_price orderMoney, ");
		sb.append("     a.ar_shortname arShortname, a.ar_fullname arFullname,  ");
		sb.append("     a.proj_id projId, a.proj_name projName, a.proj_starttime projStarttime,  ");
		sb.append("     a.proj_endtime projEndtime, a.proj_status projStatus,  ");
		sb.append("     a.drug_code drugCode, a.qualityspec_name drugName,  ");
		sb.append("     a.form_name formName, a.spec_name specName, a.bus_name busName,  ");
		sb.append("     a.factor, a.unit_name unitName, a.material_name materialName,  ");
		sb.append("     a.prod_compcode prodCompcode, a.prod_compname prodCompname,  ");
		sb.append("     a.prod_compstatus prodCompstatus, a.cat_comp_code catCompCode,  ");
		sb.append("     a.cat_comp_name catCompName, a.cat_comp_status catCompStatus,  ");
		sb.append("     a.use_range useRange, a.drug_attr drugAttr, a.goods_source goodsSource,  ");
		sb.append("     a.pattern_type patternType, a.is_healthcare isHealthcare,  ");
		sb.append("     a.is_agriculture isAgriculture, a.is_lowprice isLowprice,  ");
		sb.append("     a.is_smallprice isSmallprice, a.is_smallsource isSmallsource,  ");
		sb.append("     a.is_longtime_plan isLongtimePlan, a.drug_status drugStatus,  ");
		sb.append("     a.drug_cat drugCat, a.pro_price_limit proPriceLimit,  ");
		sb.append("     a.retail_price_limit retailPriceLimit, a.hang_price hangPrice,  ");
		sb.append("     a.ref_price refPrice, a.healthcare_price healthcarePrice,  ");
		sb.append("     a.pic_url picUrl, a.pic_short_url picShortUrl,a.drug_medicine drugMedicine, a.`status`,  ");
		sb.append("		b.hosp_ar_fullname hospArFullname,b.hosp_name hospName,b.hosp_code hospCode,b.del_comp_code delCompCode,b.del_comp_name delCompName ");
		sb.append(" 	FROM pur_orderinfo a left join pur_orderpack b on b.order_pack_id = a.order_pack_id  ");
		sb.append("		where a.prod_compcode = :prodCompcode and (a.order_status = :orderStatus1 or a.order_status = :orderStatus2 or a.order_status = :orderStatus3) ");
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("prodCompcode", prodCompcode);
		params.put("orderStatus1", OrderDetailStatus.submit.getKey());
		params.put("orderStatus2", OrderDetailStatus.confirmed.getKey());
		params.put("orderStatus3", OrderDetailStatus.dispatched.getKey());
		
		if (StringUtils.isNotBlank(drugName)) {
			sb.append(" AND (a.qualityspec_name LIKE :drugName OR a.qualityspec_name_spel LIKE :drugName) ");
			params.put("drugName", "%" + drugName + "%");
		}
		
		if (StringUtils.isNotBlank(drugCode)) {
			sb.append(" AND a.drug_code = :drugCode ");
			params.put("drugCode", drugCode);
		}
		if (StringUtils.isNotBlank(hospName)) {
			sb.append(" AND (b.hosp_name like :hospName or b.hosp_spel_code like :hospName) ");
			params.put("hospName", "%"+hospName+"%");
		}
		if (StringUtils.isNotBlank(delCompName)) {
			sb.append(" AND (b.del_comp_name like :delCompName or b.del_comp_name_spel like :delCompName)");
			params.put("delCompName", "%"+delCompName+"%");
		}
		if (StringUtils.isNotBlank(hospAreaId)) {
			sb.append(" AND b.hosp_area_id like :hospAreaId ");
			params.put("hospAreaId", hospAreaId+"%");
		}
		

		return dao.getPageMapList(sb.toString(), params, page);

	}

	/**
	 * @category 订单明细查询[配送企业]
	 * @author 应晓川
	 * @date 2015年7月18日
	 */
	public Pagination getOrdersDetailListByComp(Pagination page) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.remarks,a.order_pack_id orderPackId,a.hosp_code hospCode,a.hosp_name hospName, ");
		sb.append(" a.hosp_ar_fullname hospArFullname, a.count, a.amount, a.is_urgent isUrgent, ");
		sb.append(" a.submit_datetime submitDatetime,a.order_status orderStatus, ");
		sb.append(" a.recp_addr recpAddr, a.recp_userphone recpUserphone, a.recp_user recpUser ");
		sb.append(" FROM pur_orderpack a WHERE a.del_comp_code = :delCompCode ");
		
		OrderPackage orderPackage = (OrderPackage) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("delCompCode", orderPackage.getDelCompCode());
		if (null!=orderPackage.getOrderStatus()) {
			sb.append(" AND a.order_status=:orderStatus ");
			params.put("orderStatus",orderPackage.getOrderStatus());
		}else{
			sb.append(" AND a.order_status != :orderStatus and a.order_status != :orderStatusNoPass and a.order_status != :orderStatusSubmit  ");
			params.put("orderStatus", OrderPackStatus.finishOrder.getKey());
			params.put("orderStatusNoPass", OrderPackStatus.notPass.getKey());
			params.put("orderStatusSubmit", OrderPackStatus.submit.getKey());
		}
		if (StringUtils.isNotBlank(orderPackage.getHospName())) {
			sb.append(" AND ( a.hosp_name LIKE :hospName OR a.hosp_spel_code LIKE :hospName ) ");
			params.put("hospName", "%" + orderPackage.getHospName() + "%");
		}
		
		if (StringUtils.isNotBlank(orderPackage.getAreaId())) {
			sb.append(" AND a.hosp_area_id LIKE :areaId ");
			params.put("areaId", AreaUtil.escapeAreaId(orderPackage.getAreaId()));
		}
		
		if (StringUtils.isNotBlank(orderPackage.getOrderPackId())) {
			sb.append(" AND a.order_pack_id = :orderPackId ");
			params.put("orderPackId", orderPackage.getOrderPackId());
		}
		if (orderPackage.getStartDate() != null) {
			sb.append(" AND DATE_FORMAT(a.submit_datetime, '%Y-%m-%d') >= :startDate ");
			params.put("startDate", DateUtil.date(orderPackage.getStartDate()));
		}
		
		if (orderPackage.getEndDate() != null) {
			sb.append(" AND DATE_FORMAT(a.submit_datetime, '%Y-%m-%d') <= :endDate ");
			params.put("endDate", DateUtil.date(orderPackage.getEndDate()));
		}
		sb.append(" order by a.submit_datetime desc ");
		return dao.getPageMapList(sb.toString(), params, page);
	}

	/**
	 * 
	 * @category 获取订单信息列表数据（医疗机构）
	 * @author 韩守松
	 * @date 2015年11月26日 
	 * @param page
	 * @return
	 */
	public Pagination getHospOderInfoDataList(Pagination page) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT t.remarks,t.order_pack_id orderPackId,t.hosp_code hospCode,t.count, ");
		sb.append(" t.amount,t.is_urgent isUrgent, ");
		sb.append(" t.submit_datetime submitDatetime,t.del_comp_code delCompCode,t.order_status orderStatus, ");
		sb.append(" t.recp_addr recpAddr, t.recp_userphone recpUserphone, t.recp_user recpUser,t.del_comp_name delCompName ");
		sb.append(" from pur_orderpack t ");
		sb.append(" where t.hosp_code=:hospCode and t.order_status != :orderStatus  ");
		OrderPackage orderPackage = (OrderPackage) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("orderStatus", OrderPackStatus.finishOrder.getKey());
		params.put("hospCode", orderPackage.getHospCode());
		
		if (StringUtils.isNotBlank(orderPackage.getOrderPackId())) {
			sb.append(" AND t.order_pack_id = :orderPackId ");
			params.put("orderPackId", orderPackage.getOrderPackId());
		}
		
		if (orderPackage.getStartDate() != null) {
			sb.append(" AND DATE_FORMAT(t.submit_datetime, '%Y-%m-%d') >= :submitDatetimeStr ");
			params.put("submitDatetimeStr", DateUtil.date(orderPackage.getStartDate()));
		}
		
		if (orderPackage.getEndDate() != null) {
			sb.append(" AND DATE_FORMAT(t.submit_datetime, '%Y-%m-%d') <= :submitDatetimeEnd ");
			params.put("submitDatetimeEnd", DateUtil.date(orderPackage.getEndDate()));
		}
		
		if(StringUtils.isNotBlank(orderPackage.getDelCompName())){
			sb.append(" and t.del_comp_name like :delCompName or t.del_comp_name_spel like :delCompName");
			params.put("delCompName", "%"+orderPackage.getDelCompName()+"%");
		}
		if (orderPackage.getOrderStatus() != null) {
			sb.append(" AND t.order_status = :orderStatusF ");
			params.put("orderStatusF", orderPackage.getOrderStatus());
		}
		sb.append(" order by t.submit_datetime desc ");
		return dao.getPageMapList(sb.toString(), params, page);
	}

	/**
	 * @category 获取订单信息详情列表数据（医疗机构）
	 * @author 韩守松
	 * @date 2015年11月26日 
	 * @param page
	 * @return
	 */
	public Pagination getHospOderInfoDetailDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT t.order_code,t.pro_price,t.pro_count,(t.pro_price*t.pro_count) as buyprice,")
				.append(" a.del_count,(a.del_count*t.pro_price) as delPrice, ")
				.append(" a.recp_count,(t.pro_price*a.recp_count) as recpPrice, ")
				.append(" t.goods_code,t.drug_code,t.qualityspec_name,t.form_name,t.spec_name,t.factor,t.unit_name, ")
				.append(" t.material_name,t.prod_compname,t.prod_compcode,")
				.append(" t.is_longtime_plan,t.order_status, t.pro_price_limit ")
				.append(" from pur_orderinfo t    ")
				.append(" left join pur_delorderinfo a on a.order_code=t.order_code ")
				.append(" where t.order_pack_id=:orderPackId");
		PurOrderInfoForm form = (PurOrderInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("orderPackId", form.getOrderPackId());
		if (StringUtils.isNotBlank(form.getGoodsCode())) {
			sql.append(" and t.goods_code=:goodsCode");
			params.put("goodsCode", form.getGoodsCode());
		}
		if (StringUtils.isNotBlank(form.getBakDrugFlag())) {
			sql.append(" and t.is_longtime_plan = :bakDrugFlag");
			params.put("bakDrugFlag", form.getBakDrugFlag());
		}
		if (StringUtils.isNotBlank(form.getOrderStatus())) {
			sql.append(" and t.order_status=:orderStatus");
			params.put("orderStatus", form.getOrderStatus());
		}
		if (StringUtils.isNotBlank(form.getDrugCode())) {
			sql.append(" and t.drug_code=:drugCode");
			params.put("drugCode", form.getDrugCode());
		}
		if (StringUtils.isNotBlank(form.getDrugName())) {
			if (PinYin.isPinYin(form.getDrugName().trim())) {
				sql.append(" and t.drug_name_spel like :drugName");
				params.put("drugName", "%"
						+ form.getDrugName().trim().toUpperCase() + "%");
			} else {
				sql.append(" and t.drug_name like :drugName");
				params.put("drugName", "%" + form.getDrugName().trim() + "%");
			}
		}
		if (StringUtils.isNotBlank(form.getCompName())) {
			sql.append(" and t.prod_compname like :compName");
			params.put("compName", "%" + form.getCompName().trim() + "%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 获取物流信息（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月10日 下午2:12:40
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getOrderLogisticsData(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select a.join_plan_time,f.submit_datetime,f.order_confirm_time,c.del_datetime,b.recp_datetime ")
				.append(" from pur_orderinfo a ")
				.append(" left join pur_orderpack f on f.order_pack_id = a.order_pack_id ")
				.append(" left join pur_delorderinfo b on a.order_pack_id=b.interflow_id")
				.append(" left join pur_orderlogistics c on c.interflow_id=a.order_pack_id")
				.append(" where a.order_pack_id=:orderPackId ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("orderPackId", page.getConditions().get("orderPackId"));
		List<Object[]> list = (List<Object[]>) dao.getSqlList(sql.toString(),
				params);
		sql = new StringBuffer()
				.append("SELECT a.recp_datetime as recpDatetime,a.mid_desc as interflowDetail")
				.append(" from pur_orderlogistics a ")
				.append(" where a.interflow_id=:interflowId")
				.append(" ORDER BY a.recp_datetime desc");
		params = new HashMap<String, Object>();
		params.put("interflowId", page.getConditions().get("orderPackId"));
		page = dao.getSqlPageMapList(sql.toString(), params, page);
		if (null != list && list.size() > 0) {
			PurOrderInfoForm form = new PurOrderInfoForm();
			form.setOrderPackId(page.getConditions().get("orderPackId"));
			Object[] obj = list.get(0);
			form.setJoinPlanTime(obj[0] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[0]
							.toString())));
			if (null != obj[0] && !"".equals(obj[0].toString())) {
				if (null != obj[1] && !"".equals(obj[1].toString())) {
					form.setJoinStatus("ready");
				} else {
					form.setJoinStatus("doing");
				}
			} else {
				form.setJoinStatus("wait");
			}
			form.setSubmitDatetime(obj[1] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[1]
							.toString())));
			if (null != obj[1] && !"".equals(obj[1].toString())) {
				if (null != obj[2] && !"".equals(obj[2].toString())) {
					form.setSubStatus("ready");
				} else {
					form.setSubStatus("doing");
				}
			} else {
				form.setSubStatus("wait");
			}
			form.setOrderConfirmTime(obj[2] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[2]
							.toString())));
			if (null != obj[2] && !"".equals(obj[2].toString())) {
				if (null != obj[3] && !"".equals(obj[3].toString())) {
					form.setConfirmStatus("ready");
				} else {
					form.setConfirmStatus("doing");
				}
			} else {
				form.setConfirmStatus("wait");
			}
			form.setDelDatetime(obj[3] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[3]
							.toString())));
			if (null != obj[3] && !"".equals(obj[3].toString())) {
				if (null != obj[4] && !"".equals(obj[4].toString())) {
					form.setDelStatus("ready");
				} else {
					form.setDelStatus("doing");
				}
			} else {
				form.setDelStatus("wait");
			}
			form.setRecpDatetime(obj[4] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[4]
							.toString())));
			if (null != obj[4] && !"".equals(obj[4].toString())) {
				form.setRecpStatus("ready");
			} else {
				form.setRecpStatus("wait");
			}
			page.setForm(form);
		}
		return page;
	}

	/**
	 * 
	 * @category 获取医疗机构确认收货页面数据（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月13日 下午2:20:42
	 * @param page
	 * @return
	 */
	public Pagination getConfirmRecpDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
		.append("SELECT t.remarks,t.order_pack_id orderPackId,a.comp_name delCompName,t.submit_datetime submitDatetime,t.count goodsNum,")
		.append(" t.amount totalProPrice,t.order_beg_time orderBegTime, t.order_end_time orderEndTime, ")
		.append(" t.order_confirm_time orderConfirmTime,t.order_status orderStatus, t.order_conf_end_time orderConfEndTime, ")
		.append(" t.order_del_end_time orderDelEndTime, a.comp_id as delCompId ")
		.append(" from pur_orderpack t ")
		.append(" left join std_compinfo a on t.del_comp_code=a.comp_code  and a.status = 1 ")
		.append(" where t.hosp_code = :hospCode  ")
		.append(" and t.order_pack_id in (select purdel.order_pack_id from pur_delorderinfo purdel where purdel.is_recepted = 0) ")
		.append(" and (t.order_status = 3 or t.order_status = 4) ");
		Map<String, Object> params = new HashMap<String, Object>();
		PurOrderInfoForm form = (PurOrderInfoForm) page.getForm();
		params.put("hospCode", form.getHospCode());
		if (0 == form.getMain()) {
			sql.append(" and t.submit_user=:submitUser");
			params.put("submitUser", form.getSubmitUser());
		}
		if (StringUtils.isNotBlank(form.getOrderPackId())) {
			sql.append(" and t.order_pack_id=:orderPackId");
			params.put("orderPackId", form.getOrderPackId());
		}
		if (StringUtils.isNotBlank(form.getDelCompName())) {
			sql.append(" and (a.comp_name like :delCompName or a.comp_spel_code like :compSpelCode)");
			params.put("delCompName", "%" + form.getDelCompName().trim() + "%");
			params.put("compSpelCode", "%"
					+ form.getDelCompName().trim().toUpperCase() + "%");
		}
		if (StringUtils.isNotBlank(form.getSubmitStartDatetime())) {
			sql.append(" and t.submit_datetime >= :submitStartDatetime");
			params.put("submitStartDatetime", ParseDate.dateToDateTime(DateUtil.parseDate(form.getSubmitStartDatetime())));
		}
		if (StringUtils.isNotBlank(form.getSubmitEndDatetime())) {
			sql.append(" and t.submit_datetime <= :submitEndDatetime");
			params.put("submitEndDatetime", ParseDate.dateToEndDateTime(DateUtil.parseDate(form.getSubmitStartDatetime())));
		}

		sql.append(" order by t.submit_datetime desc ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	 /**
	  * @category 获取医疗机构确认收货订单明细页面数据（医疗机构）
	  * @author 韩守松
	  * @date   2015年11月29日
	  * @param  @param page
	  * @param  @return
	  */
	 public Pagination getConfirmRecpOrderDataList(Pagination page){
		 StringBuffer sql = new StringBuffer()
			.append("select  purdel.approve_code,purdel.del_count,purdel.invoice_no,purdel.invoice_amount,")
			.append(" purdel.invoice_datetime,purdel.credential,purdel.remarks,purdel.is_recepted,purdel.del_detail_id ")
			.append(" from pur_orderinfo purinfo  ")
			.append(" join pur_delorderinfo purdel on purinfo.order_code = purdel.order_code and purdel.is_recepted = 0 ")
			.append("  where purinfo.order_status = 3 and purinfo.order_pack_id = :orderPackId and purinfo.order_code= :orderCode ");
			Map<String, Object> params = new HashMap<String, Object>();
			PurOrderInfoForm form = (PurOrderInfoForm) page.getForm();
			params.put("orderPackId", form.getOrderPackId());
			params.put("orderCode", form.getOrderCode());
		 return dao.getSqlPageMapList(sql.toString(), params, page);
	 }
	
	/**
	 * 
	 * @category 获取确认订单详情页面（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月13日 下午2:50:39
	 * @param page
	 * @return
	 */
	public Pagination getConfirmRecpDetailDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append(" select orderinfo.order_code,orderinfo.pro_price,orderinfo.pro_count,(orderinfo.pro_price*orderinfo.pro_count) as buyprice,")
				.append(" orderinfo.goods_code,orderinfo.drug_code,orderinfo.qualityspec_name,")
				.append(" orderinfo.form_name,orderinfo.spec_name,orderinfo.factor,orderinfo.unit_name,")
				.append(" orderinfo.material_name,orderinfo.prod_compname,orderinfo.prod_compname_spel,orderinfo.order_pack_id,  ")
				.append(" orderinfo.prod_compcode,orderinfo.is_longtime_plan,orderinfo.pro_price_limit, ")
				.append(" a.del_count,(a.del_count*orderinfo.pro_price) as delPrice, ")
				.append(" a.recp_count,(orderinfo.pro_price*a.recp_count) as recpPrice, ")
				.append(" a.is_recepted,a.del_detail_id,a.approve_code,a.invoice_amount,a.invoice_datetime,orderinfo.drug_medicine  ")
				.append(" from  pur_orderinfo orderinfo  ")
				.append(" join pur_delorderinfo a on a.order_code=orderinfo.order_code ")
				.append(" where orderinfo.order_pack_id = :orderPackId and a.is_recepted = 0 and orderinfo.order_status = 3 ");
				
		Map<String, Object> params = new HashMap<String, Object>();
		PurOrderInfoForm form = (PurOrderInfoForm) page.getForm();
		params.put("orderPackId", page.getConditions().get("orderPackId"));
		
		if (StringUtils.isNotBlank(form.getDrugCode())) {
			sql.append(" and orderinfo.drug_code = :drugCode ");
			params.put("drugCode", form.getDrugCode());
		}
		if (StringUtils.isNotBlank(form.getCompName())) {
			sql.append(" and (orderinfo.prod_compname like :prodCompname or orderinfo.prod_compname_spel like :prodCompname ) ");
			params.put("prodCompname", "%" + form.getCompName().trim() + "%");
			
		}
		if (StringUtils.isNotBlank(form.getDrugName())) {
			sql.append(" and (orderinfo.qualityspec_name like :drugName or orderinfo.qualityspec_name_spel like :drugName) ");
			params.put("drugName", "%" + form.getDrugName().trim() + "%");

		}
		sql.append("  group by  orderinfo.order_code  ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 根据勾选的配送信息ID，获取所有的配送信息（医疗机构，为验证确认收货时到货数量不能大于配送数量）
	 * @author 邱磊
	 * @date 2015年7月13日 下午3:30:26
	 * @param list
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> getDelInfoList(List<DispatchInfo> list) {
		if (null != list && list.size() > 0) {
			List<String> strList = new ArrayList<String>();
			for (int i = 0; i < list.size(); i++) {
				DispatchInfo form = list.get(i);
				strList.add(form.getDelDetailId());
			}
			StringBuffer sql = new StringBuffer().append(
					"select a.del_detail_id,a.del_count from pur_delorderinfo a")
					.append(" where a.del_detail_id in (:param)");
			Map<String, Object> params = new HashMap<String, Object>();
			params.put("param", strList);
			return (List<Map<String, Object>>) dao.getSqlMapList(
					sql.toString(), params);
		}
		return null;
	}

	/**
	 * 
	 * @category 确认收货按钮操作（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月13日 下午5:06:47
	 * @param list
	 */
	public void confirmRecp(List<PurOrderInfoForm> list) {
		if (null != list && list.size() > 0) {
			/*
			 * 锁定操作行
			 */
			Integer result = lockRows(list);
			if(result ==  list.size() ){
				for(int i=0;i<list.size();i++ ){
					List<PurOrderInfoForm> orderDetailList = list.get(i).getOrderDetailInfoList();//一个订单明细对应的多个批号
					if(null != orderDetailList && orderDetailList.size() > 0){
						String orderCode = list.get(i).getOrderDetailInfoList().get(0).getOrderCode();
						String orderPackId = list.get(i).getOrderDetailInfoList().get(0).getOrderPackId();
						StringBuffer sql;
						List <String> delDetailIdList = new ArrayList<String>();
						for (int j = 0; j < orderDetailList.size(); j++) {
							Map<String, Object> params = new HashMap<String, Object>();
							PurOrderInfoForm form = orderDetailList.get(j);
							delDetailIdList.add(form.getDelDetailId());
							//更改配送信息表状态
							sql = new StringBuffer()
									.append("update pur_delorderinfo ")
									.append("set recp_count=:recpCount,is_recepted=1,recp_datetime=SYSDATE(),")
									.append("recp_user=:recpUser,store_user=:storeUser")
									.append(" where del_detail_id=:delDetailId");
							params.put("recpCount", form.getRecpCount());
							params.put("recpUser", form.getRecpUser());
							params.put("storeUser", form.getStoreUser());
							params.put("delDetailId", form.getDelDetailId());
							dao.executeSql(sql.toString(), params);
						}
							
							//更改订单明细表状态
							Map<String, Object> orderparams = new HashMap<String, Object>();
							sql = new StringBuffer()
							.append("update pur_orderinfo set order_status=5 where order_code=:ordercode");
							orderparams.put("ordercode", orderCode);
							dao.executeSql(sql.toString(), orderparams);
							
							//入到货信息表
							for (int j = 0; j < orderDetailList.size(); j++) {
							 addAoginfoByOrderCode(orderCode,orderDetailList.get(j).getApproveCode());
							}
							
							//更改订单包状态
							if(OrderPackStatus.finashDispatched.getKey() == getOrderPackStatus(orderPackId)){//判断包的原来状态是否是完成配送
								if(0 == getOrderStatusCount(orderPackId)){
									Map<String, Object> orderPackparams = new HashMap<String, Object>();
									sql = new StringBuffer()
									.append("update pur_orderpack ")
									.append("set order_status = 5 ")
									.append(" where order_pack_id=:orderPackId");
									orderPackparams.put("orderPackId", orderPackId);
									dao.executeSql(sql.toString(), orderPackparams);
								}
							}
					}
				 }
				}else{
					throw new MSException("订单正在操作中，请刷新后重试！");
				}
			}
			
		}
	
	/**
	 * @category 锁定操作行
	 * @author 韩守松
	 * @date   2015年12月15日
	 * @param  @param list
	 * @param  @return
	 */
	private Integer lockRows(List<PurOrderInfoForm> list){
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT count(*) FROM pur_orderinfo ");
		sb.append(" WHERE order_code IN (:ids) and order_status = :orderStatus  FOR UPDATE ");
		Session session = sessionFactory.getCurrentSession();
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setParameterList("ids", getIds(list));
		query.setParameter("orderStatus", OrderDetailStatus.dispatched.getKey());
		Integer result = Integer.valueOf(query.uniqueResult().toString());
		return result;
	}

	/**
	 * @category 获取操作行的订单明细id
	 * @author 韩守松
	 * @date   2015年12月15日
	 * @param  @param list
	 * @param  @return
	 */
	private List<String> getIds(List<PurOrderInfoForm> list){
		List<String> ids = new ArrayList<String>();
		if(list.size() > 0){
			for(int i = 0;i<list.size();i++){
				List<PurOrderInfoForm> orderList = list.get(i).getOrderDetailInfoList();
				if(orderList.size() > 0){
					for(int j=0;j<orderList.size();j++){
						ids.add(j,orderList.get(j).getOrderCode());
					}
				}
			}
			
		}
		return ids;
	}
	/**
	 * @category 根据订单包号获取订单报表状态
	 * @author 韩守松
	 * @date   2015年11月30日
	 * @param  @param orderPackId
	 * @param  @return
	 */
	private Integer getOrderPackStatus(String orderPackId){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("select order_status from pur_orderpack where order_pack_id = :orderPackId ");
		SQLQuery query = session.createSQLQuery(hql.toString());
		query.setString("orderPackId", orderPackId);
		String  result = query.uniqueResult().toString();
		return Integer.valueOf(result);
	}
	
	/**
	 * @category 获取配送信息表待收获状态个数
	 * @author 韩守松
	 * @date   2015年11月29日
	 * @param  @return
	 */
	private  Integer getOrderStatusCount(String orderPackId){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append("select count(*) from pur_delorderinfo where is_recepted = 0 and order_pack_id = :orderPackId ");
		SQLQuery query = session.createSQLQuery(hql.toString());
		query.setString("orderPackId", orderPackId);
		String  result = query.uniqueResult().toString();
		return Integer.valueOf(result);
	}
	
	

	/**
	 * @category 根据订单ID插入到货信息表
	 * @author 韩守松
	 * @date 2015年11月28日 
	 * @param orderId
	 */
	@SuppressWarnings("unchecked")
	private void addAoginfoByOrderCode(String orderCode,String approveCode) {
		Session session = sessionFactory.getCurrentSession();
		PurOrderInfo info = (PurOrderInfo) dao.getUniquerResult(
				PurOrderInfo.class, orderCode);
		PurOrderpack orderPack = (PurOrderpack) dao.getUniquerResult(
				PurOrderpack.class, info.getOrderPackId());
		String delHql = "from PurDelOrderInfo where orderCode=? and approveCode =?  and  isRecepted = 1 ";
		List<String> param = new ArrayList<String>();
		param.add(0,orderCode);
		param.add(1, approveCode);;
		PurDelOrderInfo delInfo = (PurDelOrderInfo) dao.getFirstResult(delHql,
				param);
		StringBuffer hql = new StringBuffer()
				// 获取已到货信息
				.append("from PurAoginfo")
				.append(" where hospCode=:hospCode and goodsCode=:goodsCode and credential=:credential ")
				.append(" and delCompCode=:delCompCode and approveCode=:approveCode and proPrice=:proPrice ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("hospCode", orderPack.getHospCode());
		params.put("goodsCode", info.getGoodsCode());
		params.put("credential", delInfo.getCredential());
		params.put("delCompCode", orderPack.getDelCompCode());
		params.put("approveCode", delInfo.getApproveCode());
		params.put("proPrice", info.getProPrice());
		List<PurAoginfo> list = (List<PurAoginfo>) dao.getList(hql.toString(),
				params);
		if (null == list || list.size() < 1) {// 没有确认收货的数据
			StringBuffer sql = new StringBuffer()
					.append("INSERT into pur_aoginfo(recp_info_id,hosp_code,hosp_name,hosp_spel_code,hosp_status,hosp_area_id,hosp_area_name,  ")
					.append(" hosp_ar_shortname,hosp_ar_fullname,user_id,goods_code,del_comp_code,del_comp_name,del_comp_name_spel,")
					.append(" del_comp_status,approve_code,credential,pro_price,total_amount,order_del_timespace,confirm_del_timespace,")
					.append(" del_recp_timespace,upd_time,upd_user,area_id,area_name,ar_shortname,ar_fullname,proj_id,proj_name, ")
					.append(" proj_starttime,proj_endtime,proj_status,qualityspec_id,drug_code,qualityspec_name,qualityspec_name_spel,")
					.append(" qualityspec_name_eng,form_name,spec_name,qualityspec_status,bus_name,factor,unit_name,material_name,drug_medicine,")
					.append(" prod_compcode,prod_compname,prod_compname_spel,prod_compstatus,cat_comp_code,cat_comp_name,")
					.append(" cat_comp_name_spel,cat_comp_status,use_range,drug_attr,goods_source,pattern_type,is_healthcare,")
					.append(" is_agriculture,is_lowprice,is_smallprice,is_smallsource,is_longtime_plan,drug_status,drug_cat,pro_price_limit,")
					.append(" retail_price_limit,hang_price,ref_price,healthcare_price,pic_url,pic_short_url,status,return_count) ")
					.append(" values( :recp_info_id , :hosp_code , :hosp_name , :hosp_spel_code , :hosp_status , :hosp_area_id , ")
					.append(" :hosp_area_name , :hosp_ar_shortname , :hosp_ar_fullname , :user_id , :goods_code , :del_comp_code , ")
					.append(" :del_comp_name , :del_comp_name_spel , :del_comp_status , :approve_code , :credential, :pro_price , :total_amount ,")
					.append(" :order_del_timespace , :confirm_del_timespace , :del_recp_timespace , :upd_time , :upd_user ,")
					.append(" :area_id , :area_name , :ar_shortname , :ar_fullname , :proj_id , :proj_name , ")
				    .append(" :proj_starttime , :proj_endtime , :proj_status , :qualityspec_id , :drug_code , :qualityspec_name , :qualityspec_name_spel , ")
					.append(" :qualityspec_name_eng , :form_name , :spec_name , :qualityspec_status , :bus_name , :factor , :unit_name , :material_name , :drug_medicine, ")
					.append(" :prod_compcode , :prod_compname , :prod_compname_spel , :prod_compstatus , :cat_comp_code , :cat_comp_name , ")
					.append(" :cat_comp_name_spel , :cat_comp_status , :use_range , :drug_attr , :goods_source , :pattern_type , :is_healthcare , ")
					.append(" :is_agriculture , :is_lowprice , :is_smallprice , :is_smallsource , :is_longtime_plan , :drug_status , :drug_cat , :pro_price_limit , ")
					.append(" :retail_price_limit , :hang_price , :ref_price , :healthcare_price , :pic_url , :pic_short_url , :status , :return_count ) ");
			SQLQuery query = session.createSQLQuery(sql.toString());
			query.setString("recp_info_id",UUID.randomUUID().toString());
			query.setString("hosp_code", orderPack.getHospCode());
			query.setString("hosp_name", orderPack.getHospName());
			query.setString("hosp_spel_code",orderPack.getHospSpelCode() );
			query.setInteger("hosp_status", orderPack.getHospStatus());
			query.setString("hosp_area_id",orderPack.getHospAreaId() );
			query.setString("hosp_area_name",orderPack.getHospAreaName() );
			query.setString("hosp_ar_shortname", orderPack.getHospArShortname());
			query.setString("hosp_ar_fullname", orderPack.getHospArFullname());
			query.setString("user_id",orderPack.getUserId());
			query.setString("goods_code",info.getGoodsCode() );
			query.setString("del_comp_code",orderPack.getDelCompCode());
			query.setString("del_comp_name", orderPack.getDelCompName());
			query.setString("del_comp_name_spel",orderPack.getDelCompNameSpel() );
			query.setInteger("del_comp_status",orderPack.getDelCompStatus());
			query.setString("approve_code", delInfo.getApproveCode());
			query.setString("credential", delInfo.getCredential());
			query.setDouble("pro_price",info.getProPrice());
			query.setInteger("total_amount",delInfo.getRecpCount() );
			query.setInteger("order_del_timespace",delInfo.getOrderDelTimespace());
			query.setInteger("confirm_del_timespace",delInfo.getConfirmDelTimespace());
			query.setInteger("del_recp_timespace",Integer.valueOf(DateUtil.getDifftime(delInfo.getDelDateTime()).toString()));
			query.setDate("upd_time",new Date());
			query.setString("upd_user",orderPack.getRecpUser());
			query.setString("area_id",info.getAreaId());
			query.setString("area_name",info.getAreaName());
			query.setString("ar_shortname",info.getArShortname());
			query.setString("ar_fullname",info.getArFullname());
			query.setString("proj_id",info.getProjId());
			query.setString("proj_name",info.getProjName());
			query.setDate("proj_starttime",info.getProjStarttime());
			query.setDate("proj_endtime",info.getProjEndtime());
			query.setInteger("proj_status",info.getProjStatus());
			query.setString("qualityspec_id",info.getQualityspecId());
			query.setString("drug_code",info.getDrugCode());
			query.setString("qualityspec_name",info.getQualityspecName());
			query.setString("qualityspec_name_spel",info.getQualityspecNameSpel());
			query.setString("qualityspec_name_eng",info.getQualityspecNameEng());
			query.setString("form_name",info.getFormName());
			query.setString("spec_name",info.getSpecName());
			query.setInteger("qualityspec_status",info.getQualityspecStatus());
			query.setString("bus_name",info.getBusName());
			query.setInteger("factor",info.getFactor());
			query.setString("unit_name",info.getUnitName());
			query.setString("material_name",info.getMaterialName());
			query.setString("drug_medicine",info.getDrugMedicine());
			query.setString("prod_compcode",info.getProdCompcode());
			query.setString("prod_compname",info.getProdCompname());
			query.setString("prod_compname_spel",info.getProdCompnameSpel());
			query.setInteger("prod_compstatus",info.getProdCompstatus());
			query.setString("cat_comp_code",info.getCatCompCode());
			query.setString("cat_comp_name",info.getCatCompName());
			query.setString("cat_comp_name_spel",info.getCatCompNameSpel());
			query.setInteger("cat_comp_status",info.getCatCompStatus());
			query.setInteger("use_range",info.getUseRange());
			query.setInteger("drug_attr",info.getDrugAttr());
			query.setInteger("goods_source",info.getGoodsSource());
			query.setInteger("pattern_type",info.getPatternType());
			query.setInteger("is_healthcare",info.getIsHealthcare());
			query.setInteger("is_agriculture",info.getIsAgriculture());
			query.setInteger("is_lowprice",info.getIsLowprice());
			query.setInteger("is_smallprice",info.getIsSmallprice());
			query.setInteger("is_smallsource",info.getIsSmallsource());
			if(null != info.getIsLongtimePlan()){
				query.setInteger("is_longtime_plan",info.getIsLongtimePlan());
			}else{
				query.setBigInteger("is_longtime_plan",null);
			}
			query.setInteger("drug_status",info.getDrugStatus());
			query.setInteger("drug_cat",info.getDrugCat());
			query.setDouble("pro_price_limit",info.getProPriceLimit());
			query.setDouble("retail_price_limit",info.getRetailPriceLimit());
			query.setDouble("hang_price",info.getHangPrice());
			query.setDouble("ref_price",info.getRefPrice());
			query.setDouble("healthcare_price",info.getHealthcarePrice());
			query.setString("pic_url",info.getPicUrl());
			query.setString("pic_short_url",info.getPicShortUrl());
			query.setInteger("status",info.getStatus());
			query.setInteger("return_count",0);
			query.executeUpdate();
		} else {
			PurAoginfo dataAoginfo = list.get(0);
			dataAoginfo.setTotalAmount(dataAoginfo.getTotalAmount() + delInfo.getRecpCount());
			dao.update(dataAoginfo);
		}
		session.flush();
		session.clear();

	}

	/**
	 * 
	 * @category 未到货功能操作（医疗机构）
	 * @author 韩守松
	 * @date 2015年11月30日 
	 * @param list
	 */
	public void notArrived(List<PurOrderInfoForm> list) {
		if (null != list && list.size() > 0) {
			for(int i=0;i<list.size();i++ ){
				List<PurOrderInfoForm> orderDetailList = list.get(i).getOrderDetailInfoList();//一个订单明细对应的多个批号
				if(null != orderDetailList && orderDetailList.size() > 0){
					String orderCode = list.get(i).getOrderDetailInfoList().get(0).getOrderCode();
					String orderPackId = list.get(i).getOrderDetailInfoList().get(0).getOrderPackId();
					StringBuffer sql;
					List <String> delDetailIdList = new ArrayList<String>();
					for (int j = 0; j < orderDetailList.size(); j++) {
						Map<String, Object> params = new HashMap<String, Object>();
						PurOrderInfoForm form = orderDetailList.get(j);
						delDetailIdList.add(form.getDelDetailId());
						//更改配送信息表状态
						sql = new StringBuffer()
								.append("update pur_delorderinfo ")
								.append("set recp_count = 0 ,is_recepted=2,recp_datetime=SYSDATE(),")
								.append("recp_user=:recpUser,store_user=:storeUser")
								.append(" where del_detail_id=:delDetailId");
						params.put("recpUser", form.getRecpUser());
						params.put("storeUser", form.getStoreUser());
						params.put("delDetailId", form.getDelDetailId());
						dao.executeSql(sql.toString(), params);
					}
						
						//更改订单明细表状态
						Map<String, Object> orderparams = new HashMap<String, Object>();
						sql = new StringBuffer()
						.append("update pur_orderinfo set order_status=6 where order_code=:ordercode");
						orderparams.put("ordercode", orderCode);
						dao.executeSql(sql.toString(), orderparams);
						
						//更改订单包状态
						if(OrderPackStatus.finashDispatched.getKey() == getOrderPackStatus(orderPackId)){//判断包的原来状态是否是完成配送
							if(0 == getOrderStatusCount(orderPackId)){
								Map<String, Object> orderPackparams = new HashMap<String, Object>();
								sql = new StringBuffer()
								.append("update pur_orderpack ")
								.append("set order_status = 5 ")
								.append(" where order_pack_id=:orderPackId");
								orderPackparams.put("orderPackId", orderPackId);
								dao.executeSql(sql.toString(), orderPackparams);
							}
						}
				}
			 }
			}
	}


	/**
	 * 
	 * @category 获取到货明细列表（医疗机构）
	 * @author 韩守松
	 * @date 2015年11月28日 
	 * @param page
	 * @return
	 */
	public Pagination getArrivedDrug(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append(" SELECT a.user_id,a.drug_code,a.qualityspec_name,a.form_name,a.spec_name,")
				.append("  a.factor,a.unit_name,a.material_name,a.prod_compname,a.del_comp_code,a.return_count, ")
				.append("  a.del_comp_name as delCompName,a.approve_code,a.drug_medicine drugMedicine,a.credential,a.pro_price,a.total_amount,a.prod_compcode,a.proj_name ")
				.append("  from pur_aoginfo a ")
				.append(" where a.hosp_code = :hospCode ");
		PurOrderInfoForm form = (PurOrderInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("hospCode", form.getHospCode());
		if (0 == form.getMain()) {
			sql.append(" and a.user_id=:submitUser");
			params.put("submitUser", form.getSubmitUser());
		}
		if (StringUtils.isNotBlank(form.getDrugCode())) {
			sql.append(" and a.drug_code=:drugCode");
			params.put("drugCode", form.getDrugCode());
		}
		if (StringUtils.isNotBlank(form.getDrugName())) {
			if (PinYin.isPinYin(form.getDrugName().trim())) {
				sql.append(" and a.qualityspec_name_spel like :drugName");
				params.put("drugName", "%"
						+ form.getDrugName().trim().toUpperCase() + "%");
			} else {
				sql.append(" and a.qualityspec_name like :drugName");
				params.put("drugName", "%" + form.getDrugName().trim() + "%");
			}
		}
		if (StringUtils.isNotBlank(form.getCompName())) {
			sql.append(" and a.prod_compname like :compName or a.prod_compname_spel like :compName ");
			params.put("compName", "%" + form.getCompName().trim() + "%");
		}
		if (StringUtils.isNotBlank(form.getDelCompName())) {
			sql.append(" and a.del_comp_name like :delCompName or a.del_comp_name_spel like :delCompName");
			params.put("delCompName", "%" + form.getDelCompName().trim() + "%");
		}
		sql.append(" order by a.upd_time desc");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 整包确认收货（医疗机构操作，所有到货数量就是配送数量）
	 * @author 邱磊
	 * @date 2015年7月14日 上午9:38:00
	 * @param page
	 */
	@SuppressWarnings("unchecked")
	public void confirmByPack(Pagination page) {
		String[] orderPackIdList = page.getConditions().get("orderPackIdList").split(",");
		for (String orderPackId : orderPackIdList) {
			if (StringUtils.isNotBlank(orderPackId)) {
				StringBuffer sql = new StringBuffer()
						.append("update pur_delorderinfo set recp_count=del_count,recp_datetime=SYSDATE(),")
						.append("recp_user=:recpUser,store_user=:storeUser,is_recepted=1")
						.append(" where interflow_id=:orderPackId and is_recepted=0");
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("recpUser", page.getConditions().get("recpUser"));
				params.put("storeUser", page.getConditions().get("storeUser"));
				params.put("orderPackId", orderPackId);
				dao.executeSql(sql.toString(), params);
				sql = new StringBuffer()
						.append("update pur_orderinfo set order_status=5 where order_pack_id=:orderPackId and order_status=4");
				params.remove("recpUser");
				params.remove("storeUser");
				dao.executeSql(sql.toString(), params);
				sql = new StringBuffer()
						.append("from PurOrderInfo where orderStatus=5 and orderPackId=:orderPackId");
				List<PurOrderInfo> list = (List<PurOrderInfo>) dao.getList(
						sql.toString(), params);
				if (null != list && list.size() > 0) {
					for (int i = 0; i < list.size(); i++) {
						PurOrderInfo data = list.get(i);
						addAoginfoByOrderCode(data.getOrderPackId(),"");
					}
				}
			}
		}

	}

	/**
	 * 
	 * @category 整包未到货操作（医疗机构）
	 * @author 邱磊
	 * @date 2015年7月14日 上午10:16:35
	 * @param page
	 */
	public void noArrivedByPack(Pagination page) {

		String[] orderPackIdList = page.getConditions().get("orderPackIdList").split(",");
		for (String orderPackId : orderPackIdList) {
			if (StringUtils.isNotBlank(orderPackId)) {

				StringBuffer sql = new StringBuffer()
						.append("update pur_delorderinfo set recp_datetime=SYSDATE(),")
						.append(" recp_user=:recpUser,is_recepted=2")
						.append(" where interflow_id=:orderPackId and is_recepted=0");
				Map<String, Object> params = new HashMap<String, Object>();
				params.put("recpUser", page.getConditions().get("recpUser"));
				params.put("orderPackId", orderPackId);
				dao.executeSql(sql.toString(), params);
				sql = new StringBuffer()
						.append("update pur_orderinfo set order_status=6 where order_pack_id=:orderPackId and order_status=4");
				params.remove("recpUser");
				dao.executeSql(sql.toString(), params);
				/*moveOrderInfoOld(orderPackId);
				moveDelOrderInfoOld(orderPackId);
				moveOrderLogisticsOld(orderPackId);*/
			}
		}
	}


	/**
	 * 
	 * @category 根据订单明细ID查询物流信息
	 * @author 邱磊
	 * @date 2015年7月14日 上午10:51:11
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination toDeliverDetailById(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select a.join_plan_time,f.submit_datetime,f.order_confirm_time,c.del_datetime,b.recp_datetime,a.order_pack_id ")
				.append(" from pur_orderinfo a ")
				.append(" left join pur_orderpack f on f.order_pack_id = a.order_pack_id")
				.append(" left join pur_delorderinfo b on a.order_pack_id=b.interflow_id")
				.append(" left join pur_orderlogistics c on c.interflow_id=a.order_pack_id")
				.append(" where a.order_code=:orderId");
		Map<String, String> params = new HashMap<String, String>();
		params.put("orderId", page.getConditions().get("orderId"));
		List<Object[]> list = (List<Object[]>) dao.getSqlList(sql.toString(),
				params);
		sql = new StringBuffer()
				.append("SELECT a.recp_datetime as recpDatetime,a.mid_desc as interflowDetail")
				.append(" from pur_orderlogistics a ")
				.append(" left join pur_orderinfo b on a.interflow_id=b.order_pack_id")
				.append(" where b.order_code=:orderId")
				.append(" ORDER BY a.recp_datetime desc");
		params = new HashMap<String, String>();
		params.put("orderId", page.getConditions().get("orderId"));
		page = dao.getSqlPageMapList(sql.toString(), params, page);
		if (null != list && list.size() > 0) {
			PurOrderInfoForm form = new PurOrderInfoForm();
			Object[] obj = list.get(0);
			form.setOrderPackId(obj[5] == null ? "" : obj[5].toString());
			form.setJoinPlanTime(obj[0] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[0]
							.toString())));
			if (null != obj[0] && !"".equals(obj[0].toString())) {
				if (null != obj[1] && !"".equals(obj[1].toString())) {
					form.setJoinStatus("ready");
				} else {
					form.setJoinStatus("doing");
				}
			} else {
				form.setJoinStatus("wait");
			}
			form.setSubmitDatetime(obj[1] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[1]
							.toString())));
			if (null != obj[1] && !"".equals(obj[1].toString())) {
				if (null != obj[2] && !"".equals(obj[2].toString())) {
					form.setSubStatus("ready");
				} else {
					form.setSubStatus("doing");
				}
			} else {
				form.setSubStatus("wait");
			}
			form.setOrderConfirmTime(obj[2] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[2]
							.toString())));
			if (null != obj[2] && !"".equals(obj[2].toString())) {
				if (null != obj[3] && !"".equals(obj[3].toString())) {
					form.setConfirmStatus("ready");
				} else {
					form.setConfirmStatus("doing");
				}
			} else {
				form.setConfirmStatus("wait");
			}
			form.setDelDatetime(obj[3] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[3]
							.toString())));
			if (null != obj[3] && !"".equals(obj[3].toString())) {
				if (null != obj[4] && !"".equals(obj[4].toString())) {
					form.setDelStatus("ready");
				} else {
					form.setDelStatus("doing");
				}
			} else {
				form.setDelStatus("wait");
			}
			form.setRecpDatetime(obj[4] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[4]
							.toString())));
			if (null != obj[4] && !"".equals(obj[4].toString())) {
			} else {
				form.setRecpStatus("wait");
			}
			page.setForm(form);
		}
		return page;
	}

	/**
	 * 
	 * @category 根据订单明细ID查询物流信息
	 * @author 徐国鹏
	 * @date 2015年8月7日 下午14:51:11
	 * @param page
	 * @return
	 */
	public Pagination toDeliverDetailByIdOld(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select a.join_plan_time,a.submit_datetime,a.order_confirm_time,c.del_datetime,b.recp_datetime,a.order_pack_id ")
				.append(" from pur_orderinfo_old a   ")
				.append(" left join pur_delorderinfo_old b on a.order_pack_id=b.interflow_id")
				.append(" left join pur_orderlogistics_old c on c.interflow_id=a.order_pack_id ")
				.append(" where a.order_id=:orderId");
		Map<String, String> params = new HashMap<String, String>();
		params.put("orderId", page.getConditions().get("orderId"));
		@SuppressWarnings("unchecked")
		List<Object[]> list = (List<Object[]>) dao.getSqlList(sql.toString(),
				params);
		sql = new StringBuffer()
				.append("SELECT a.recp_datetime as recpDatetime,a.interflow_detail as interflowDetail")
				.append(" from pur_orderlogistics_old a ")
				.append(" left join pur_orderinfo_old b on a.interflow_id=b.order_pack_id")
				.append(" where b.order_id=:orderId")
				.append(" ORDER BY a.recp_datetime desc");
		params = new HashMap<String, String>();
		params.put("orderId", page.getConditions().get("orderId"));
		page = dao.getSqlPageMapList(sql.toString(), params, page);
		if (null != list && list.size() > 0) {
			PurOrderInfoForm form = new PurOrderInfoForm();
			Object[] obj = list.get(0);
			form.setOrderPackId(obj[5] == null ? "" : obj[5].toString());
			form.setJoinPlanTime(obj[0] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[0]
							.toString())));
			if (null != obj[0] && !"".equals(obj[0].toString())) {
				if (null != obj[1] && !"".equals(obj[1].toString())) {
					form.setJoinStatus("ready");
				} else {
					form.setJoinStatus("doing");
				}
			} else {
				form.setJoinStatus("wait");
			}
			form.setSubmitDatetime(obj[1] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[1]
							.toString())));
			if (null != obj[1] && !"".equals(obj[1].toString())) {
				if (null != obj[2] && !"".equals(obj[2].toString())) {
					form.setSubStatus("ready");
				} else {
					form.setSubStatus("doing");
				}
			} else {
				form.setSubStatus("wait");
			}
			form.setOrderConfirmTime(obj[2] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[2]
							.toString())));
			if (null != obj[2] && !"".equals(obj[2].toString())) {
				if (null != obj[3] && !"".equals(obj[3].toString())) {
					form.setConfirmStatus("ready");
				} else {
					form.setConfirmStatus("doing");
				}
			} else {
				form.setConfirmStatus("wait");
			}
			form.setDelDatetime(obj[3] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[3]
							.toString())));
			if (null != obj[3] && !"".equals(obj[3].toString())) {
				if (null != obj[4] && !"".equals(obj[4].toString())) {
					form.setDelStatus("ready");
				} else {
					form.setDelStatus("doing");
				}
			} else {
				form.setDelStatus("wait");
			}
			form.setRecpDatetime(obj[4] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[4]
							.toString())));
			if (null != obj[4] && !"".equals(obj[4].toString())) {
			} else {
				form.setRecpStatus("wait");
			}
			page.setForm(form);
		}
		return page;
	}

	/**
	 * 
	 * @category 根据包号查询物流信息
	 * @author 徐国鹏
	 * @date 2015年8月7日 下午14:51:11
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getOrderLogisticsDataOld(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select a.join_plan_time,a.submit_datetime,a.order_confirm_time,c.del_datetime,b.recp_datetime ")
				.append(" from pur_orderinfo_old a ")
				.append(" left join pur_delorderinfo_old b on a.order_pack_id=b.interflow_id")
				.append(" left join pur_orderlogistics_old c on c.interflow_id=a.order_pack_id")
				.append(" where a.order_pack_id=:orderPackId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("orderPackId", page.getConditions().get("orderPackId"));
		List<Object[]> list = (List<Object[]>) dao.getSqlList(sql.toString(),
				params);
		sql = new StringBuffer()
				.append("SELECT a.recp_datetime as recpDatetime,a.interflow_detail as interflowDetail")
				.append(" from pur_orderlogistics_old a ")
				.append(" where a.interflow_id=:interflowId")
				.append(" ORDER BY a.recp_datetime desc");
		params = new HashMap<String, Object>();
		params.put("interflowId", page.getConditions().get("orderPackId"));
		page = dao.getSqlPageMapList(sql.toString(), params, page);
		if (null != list && list.size() > 0) {
			PurOrderInfoForm form = new PurOrderInfoForm();
			form.setOrderPackId(page.getConditions().get("orderPackId"));
			Object[] obj = list.get(0);
			form.setJoinPlanTime(obj[0] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[0]
							.toString())));
			if (null != obj[0] && !"".equals(obj[0].toString())) {
				if (null != obj[1] && !"".equals(obj[1].toString())) {
					form.setJoinStatus("ready");
				} else {
					form.setJoinStatus("doing");
				}
			} else {
				form.setJoinStatus("wait");
			}
			form.setSubmitDatetime(obj[1] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[1]
							.toString())));
			if (null != obj[1] && !"".equals(obj[1].toString())) {
				if (null != obj[2] && !"".equals(obj[2].toString())) {
					form.setSubStatus("ready");
				} else {
					form.setSubStatus("doing");
				}
			} else {
				form.setSubStatus("wait");
			}
			form.setOrderConfirmTime(obj[2] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[2]
							.toString())));
			if (null != obj[2] && !"".equals(obj[2].toString())) {
				if (null != obj[3] && !"".equals(obj[3].toString())) {
					form.setConfirmStatus("ready");
				} else {
					form.setConfirmStatus("doing");
				}
			} else {
				form.setConfirmStatus("wait");
			}
			form.setDelDatetime(obj[3] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[3]
							.toString())));
			if (null != obj[3] && !"".equals(obj[3].toString())) {
				if (null != obj[4] && !"".equals(obj[4].toString())) {
					form.setDelStatus("ready");
				} else {
					form.setDelStatus("doing");
				}
			} else {
				form.setDelStatus("wait");
			}
			form.setRecpDatetime(obj[4] == null ? ""
					: ParseDate.parseFullFormat(ParseDate.fromFullFormat(obj[4]
							.toString())));
			if (null != obj[4] && !"".equals(obj[4].toString())) {
				form.setRecpStatus("ready");
			} else {
				form.setRecpStatus("wait");
			}
			page.setForm(form);
		}
		return page;
	}


	/**
	 * 
	 * @category 获取未评价订单列表
	 * @author 邱磊
	 * @date 2015年7月19日 下午1:42:40
	 * @param page
	 * @return
	 */
	public Pagination getUnReviewsDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT t.order_pack_id orderPackId,a.comp_name delCompName,t.submit_datetime submitDatetime,count(t.goods_code) goodsNum,")
				.append("sum(t.pro_count*t.pro_price) totalProPrice,max(t.order_beg_time) orderBegTime,f.with_order_id withOrderId,DATE_FORMAT(max(t.order_confirm_time), '%Y-%m-%d %H:%i:%S') orderConfirmTime,")
				.append("max(t.order_conf_end_time) orderConfEndTime,t.del_comp_id delCompId,d.name as userName,DATE_FORMAT(f.del_datetime, '%Y-%m-%d %H:%i:%S') delDatetime,")
				.append("count(case t.order_status when 5 THEN 1 when 6 then 1 ELSE null end) as delNum,count(case t.order_status when 5 THEN 1 ELSE null end) as recpNum,count(case t.order_status when 6 THEN 1 ELSE null end) as notRecpNum,f.recp_datetime as recpDatetime  ")
				.append(" from pur_orderinfo_old t")
				.append(" left join std_compinfo a on t.del_comp_id=a.comp_id")
				.append(" left join pur_delorderinfo b on b.order_id=t.order_id")
				.append(" left join sys_user d on d.id=t.submit_user")
				.append(" left join pur_orderlogistics_old f on t.order_pack_id=f.interflow_id")
				.append(" where f.del_datetime is not null and f.grade_flag=0");
		Map<String, Object> params = new HashMap<String, Object>();
		PurOrderInfoForm form = (PurOrderInfoForm) page.getForm();
		if (StringUtils.isNotBlank(form.getOrderPackId())) {
			sql.append(" and t.order_pack_id=:orderPackId");
			params.put("orderPackId", form.getOrderPackId().trim());
		}
		if (StringUtils.isNotBlank(form.getDelCompName())) {
			if (PinYin.isPinYin(form.getDelCompName())) {
				sql.append(" and a.comp_spel_code like :delCompName");
			} else {
				sql.append(" and a.comp_name like :delCompName");
			}
			params.put("delCompName", "%" + form.getDelCompName().trim() + "%");
		}
		if (StringUtils.isNotBlank(form.getWithOrderId())) {
			sql.append(" and f.with_order_id_id=:withOrderId");
			params.put("withOrderId", form.getOrderPackId().trim());
		}
		if (StringUtils.isNotBlank(form.getStartDate())) {
			sql.append(" and t.order_beg_time >= :startDate ");
			params.put("startDate", form.getStartDate().trim() + " 00:00:00");
		}
		if (StringUtils.isNotBlank(form.getEndDate())) {
			sql.append(" and t.order_beg_time <= :endDate ");
			params.put("endDate", form.getEndDate().trim() + " 23:59:59");
		}
		if (StringUtils.isNotBlank(form.getRecpStartDate())) {
			sql.append(" and f.recp_datetime>= :recpStartDate ");
			params.put("recpStartDate", form.getRecpStartDate().trim()
					+ " 00:00:00");
		}
		if (StringUtils.isNotBlank(form.getRecpEndDate())) {
			sql.append(" and f.recp_datetime<= :recpEndDate ");
			params.put("recpEndDate", form.getRecpEndDate().trim()
					+ " 23:59:59");
		}
		sql.append(" GROUP BY t.order_pack_id");
		sql.append(" order by f.recp_datetime desc ");
		return dao.getSqlPageMapList(sql.toString(), params, page);

	}

	/**
	 * 
	 * @category 根据物流信息ID查询物流历史信息
	 * @author 邱磊
	 * @date 2015年7月19日 下午8:43:36
	 * @param interflowId
	 * @return
	 */
	public PurOrderLogisticsOld getLogisticsInfo(String interflowId) {
		PurOrderLogisticsOld data = (PurOrderLogisticsOld) dao
				.getUniquerResult(PurOrderLogisticsOld.class, interflowId);
		return data;
	}

	/**
	 * 
	 * @category 保存订单评价信息
	 * @author 邱磊
	 * @date 2015年7月19日 下午8:49:41
	 * @param page
	 */
	public void saveReviews(Pagination page) {
		PurOrderInfoForm form = (PurOrderInfoForm) page.getForm();
		PurOrderLogisticsOld data = (PurOrderLogisticsOld) dao
				.getUniquerResult(PurOrderLogisticsOld.class,
						form.getInterflowId());
		data.setGradeFlag(1);
		data.setGradeScore(ParseNumber.toInteger(form.getGradeScore()));
		data.setGradeRemark(form.getGradeRemark());
		data.setGradeDatetime(new Date());
		data.setGradeUser(form.getGradeUser());
		dao.update(data);

	}

	/**
	 * 
	 * @category 查询已评价列表
	 * @author 邱磊
	 * @date 2015年7月20日 下午5:13:09
	 * @param page
	 * @return
	 */
	public Pagination getReviewsDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()

				.append(" SELECT f.interflow_id interflowId,a.comp_name delCompName,DATE_FORMAT(t.submit_datetime,'%Y-%m-%d %H:%i:%S') as submitDatetime,count(t.goods_code) proCount,")
				.append(" sum(t.pro_count*t.pro_price) totalProPrice,DATE_FORMAT(max(t.order_beg_time), '%Y-%m-%d %H:%i:%S') orderBegTime,")
				.append(" DATE_FORMAT(max(t.order_conf_end_time), '%Y-%m-%d %H:%i:%S') delDatetime,t.del_comp_id delCompId,d.name as userName,")
				.append(" count(case t.order_status when 4 THEN 1 ELSE null end) as delCount,count(case t.order_status when 5 THEN 1 ELSE null end) as recpCount,count(case t.order_status when 6 THEN 1 ELSE null end) as notRecpNum,DATE_FORMAT(f.recp_datetime,'%Y-%m-%d %H:%i:%S') as recpDatetime,")
				.append(" f.grade_score gradeScore,DATE_FORMAT(f.grade_datetime,'%Y-%m-%d %H:%i:%S') as gradeDatetime,f.grade_remark gradeRemark,f.with_order_id withOrderId ")
				.append(" from pur_orderinfo_old t")
				.append(" left join std_compinfo a on t.del_comp_id=a.comp_id")
				.append(" left join pur_delorderinfo_old b on b.order_id=t.order_id")
				.append(" left join sys_user d on d.id=t.submit_user")
				.append(" left join pur_orderlogistics_old f on t.order_pack_id=f.interflow_id")
				.append(" where f.del_datetime is not null and f.grade_flag=1");
		Map<String, Object> params = new HashMap<String, Object>();
		PurOrderInfoForm form = (PurOrderInfoForm) page.getForm();
		if (StringUtils.isNotBlank(form.getDelCompName())) {
			if (PinYin.isPinYin(form.getDelCompName().trim())) {
				sql.append(" and a.comp_spel_code like :delCompName");
				params.put("delCompName", "%"
						+ form.getDelCompName().trim().toUpperCase() + "%");
			} else {
				sql.append(" and a.comp_name like :delCompName");
				params.put("delCompName", "%" + form.getDelCompName().trim()
						+ "%");
			}
		}
		if (StringUtils.isNotBlank(form.getInterflowId())) {
			sql.append(" and f.interflow_id=:interflowId");
			params.put("interflowId", form.getInterflowId().trim());
		}
		if (StringUtils.isNotBlank(form.getWithOrderId())) {
			sql.append(" and f.with_order_id_id=:withOrderId");
			params.put("withOrderId", form.getOrderPackId().trim());
		}
		if (StringUtils.isNotBlank(form.getStartDate())) {
			sql.append(" and t.order_beg_time >= :startDate ");
			params.put("startDate", form.getStartDate().trim() + " 00:00:00");
		}
		if (StringUtils.isNotBlank(form.getEndDate())) {
			sql.append(" and t.order_beg_time <= :endDate ");
			params.put("endDate", form.getEndDate().trim() + " 23:59:59");
		}
		if (StringUtils.isNotBlank(form.getRecpStartDate())) {
			sql.append(" and f.recp_datetime>= :recpStartDate ");
			params.put("recpStartDate", form.getRecpStartDate().trim()
					+ " 00:00:00");
		}
		if (StringUtils.isNotBlank(form.getRecpEndDate())) {
			sql.append(" and f.recp_datetime<= :recpEndDate ");
			params.put("recpEndDate", form.getRecpEndDate().trim()
					+ " 23:59:59");
		}
		sql.append(" GROUP BY f.grade_datetime desc");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	// /////////////////////////
	/**
	 * 
	 * @category 查询已评价列表详情列表
	 * @author 徐国鹏
	 * @date 2015年8月4日 下午5:13:09
	 * @param page
	 * @return
	 */
	public Pagination getReviewsDataListDetail(Pagination page) {
		StringBuffer sql = new StringBuffer()

				.append(" SELECT f.interflow_id interflowId,a.comp_name delCompName,DATE_FORMAT(t.submit_datetime,'%Y-%m-%d %H:%i:%S') as submitDatetime,count(t.goods_code) proCount,")
				.append(" sum(t.pro_count*t.pro_price) totalProPrice,DATE_FORMAT(max(t.order_beg_time), '%Y-%m-%d %H:%i:%S') orderBegTime,")
				.append(" DATE_FORMAT(max(t.order_conf_end_time), '%Y-%m-%d %H:%i:%S') delDatetime,t.del_comp_id delCompId,d.name as userName,")
				.append(" count(case t.order_status when 4 THEN 1 ELSE null end) as delCount,count(case t.order_status when 5 THEN 1 ELSE null end) as recpCount,count(case t.order_status when 6 THEN 1 ELSE null end) as notRecpNum,DATE_FORMAT(f.recp_datetime,'%Y-%m-%d %H:%i:%S') as recpDatetime,")
				.append(" f.grade_score gradeScore,DATE_FORMAT(f.grade_datetime,'%Y-%m-%d %H:%i:%S') as gradeDatetime,f.grade_remark gradeRemark,f.with_order_id withOrderId, ")
				.append(" g.drug_id as drugId,a.comp_id as compId,a.comp_name as compName,g.drug_code as drugCode,g.drug_name as drugName,g.factor,g.unit,m.spec_name as specName,n.form_name as formName,x.material_name as materialName")
				.append(" from pur_orderinfo_old t")
				.append(" left join std_compinfo a on t.del_comp_id=a.comp_id")
				.append(" left join pur_delorderinfo_old b on b.order_id=t.order_id")
				.append(" left join sys_user d on d.id=t.submit_user")
				.append(" left join pur_orderlogistics_old f on t.order_pack_id=f.interflow_id")
				.append(" left join std_druginfo g on g.drug_id = t.drug_id")
				.append(" left join dic_drugspec m on m.spec_id=g.spec_id")
				.append(" left join dic_drugform n on n.form_id=g.form_id")
				.append(" left join dic_drugmaterial x on x.material_id=g.material_id")
				.append(" where f.del_datetime is not null and f.grade_flag=1");
		Map<String, Object> params = new HashMap<String, Object>();
		PurOrderInfoForm form = (PurOrderInfoForm) page.getForm();
		if (StringUtils.isNotBlank(form.getInterflowId())) {
			sql.append(" and f.interflow_id=:interflowId");
			params.put("interflowId", form.getInterflowId().trim());
		}
		if (StringUtils.isNotBlank(form.getDelCompName())) {
			if (PinYin.isPinYin(form.getDelCompName().trim())) {
				sql.append(" and a.comp_name like :delCompName");
				params.put("delCompName", "%"
						+ form.getDelCompName().trim().toUpperCase() + "%");
			} else {
				sql.append(" and a.comp_name like :delCompName");
				params.put("delCompName", "%" + form.getDelCompName().trim()
						+ "%");
			}
		}
		if (StringUtils.isNotBlank(form.getStartDate())) {
			sql.append(" and t.order_beg_time >= :startDate ");
			params.put("startDate", form.getStartDate().trim() + "%");
		}
		if (StringUtils.isNotBlank(form.getEndDate())) {
			sql.append(" and t.order_beg_time <= :endDate ");
			params.put("endDate", form.getEndDate().trim() + "%");
		}
		if (StringUtils.isNotBlank(form.getRecpStartDate())) {
			sql.append(" and f.recp_datetime>= :recpStartDate ");
			params.put("recpStartDate", form.getRecpStartDate().trim() + "%");
		}
		if (StringUtils.isNotBlank(form.getRecpEndDate())) {
			sql.append(" and f.recp_datetime<= :recpEndDate ");
			params.put("recpEndDate", form.getRecpEndDate().trim() + "%");
		}
		sql.append(" GROUP BY f.recp_datetime desc");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 查询完成订单信息
	 * @author 韩守松
	 * @date 2015年11月30日 
	 * @param page
	 * @return
	 */
	@Override
	public Pagination getHospOderInfoOld(Pagination page) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.remarks,a.order_pack_id orderPackId, a.hosp_code hospCode,  ");
		sb.append("  a.count, a.amount, a.is_urgent isUrgent, ");
		sb.append(" a.submit_datetime submitDatetime,a.del_comp_code delCompCode,a.del_comp_name delCompName, ");
		sb.append(" a.recp_addr recpAddr, a.recp_userphone recpUserphone, a.recp_user recpUser,a.order_status orderStatus ");
		sb.append(" FROM pur_orderpack a WHERE  ");
		sb.append(" a.order_status = :orderStatus and a.hosp_code = :hospCode ");
		
		OrderPackage orderPackage = (OrderPackage) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("hospCode", orderPackage.getHospCode());
		params.put("orderStatus", OrderPackStatus.finishOrder.getKey());
		
		if (StringUtils.isNotBlank(orderPackage.getOrderPackId())) {
			sb.append(" AND a.order_pack_id = :orderPackId ");
			params.put("orderPackId", orderPackage.getOrderPackId());
		}
		
		if (orderPackage.getStartDate() != null) {
			sb.append(" AND a.submit_datetime >= :startDate ");
			params.put("startDate", ParseDate.dateToDateTime(orderPackage.getStartDate()));
		}
		
		if (orderPackage.getEndDate() != null) {
			sb.append(" AND a.submit_datetime <= :endDate ");
			params.put("endDate", ParseDate.dateToEndDateTime(orderPackage.getEndDate()));
		}
		if(StringUtils.isNotBlank(orderPackage.getDelCompName())){
			sb.append(" and (a.del_comp_name like :delCompName or a.del_comp_name_spel like :delCompName )");
			params.put("delCompName", "%"+orderPackage.getDelCompName()+"%");
		}
		sb.append(" order by a.submit_datetime DESC ");
		return dao.getPageMapList(sb.toString(), params, page);
	}

	/**
	 * 
	 * @category 获取历史订单详情信息（医疗机构）
	 * @author 徐国鹏
	 * @date 2015年7月10日 上午11:55:51
	 * @param page
	 * @return
	 */
	public Pagination getHospOderOldDetail(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT t.order_code,t.pro_price,t.pro_count,(t.pro_price*t.pro_count) as buyprice,a.del_count,(a.del_count*t.pro_price) as delPrice,")
				.append(" a.recp_count,(t.pro_price*a.recp_count) as recpPrice,t.goods_code,c.drug_code,c.drug_name,c.form_name,c.spec_name,c.factor,c.unit,")
				.append(" c.material_name,c.comp_name,t.drug_id,e.comp_name as delCompName,t.del_comp_id,c.prod_comp_id,t.bak_drug_flag,t.is_longtime_plan,t.order_status,")
				.append(" ds.dic_name,t.pro_price_limit ")
				.append(" from pur_orderinfo_old t")
				.append(" left join pur_delorderinfo_old a on a.order_id=t.order_id")
				.append(" left join view_drug c on c.drug_id=t.drug_id")
				.append(" left join std_compinfo e on e.comp_id=t.del_comp_id")
				.append(" left join base_goods bg on t.goods_code=bg.goods_code")
				.append(" left join dic_sysdic ds on t.archive_type =ds.dic_code  AND ds.classify_code = ")
				.append(Constants.ARCHIVE_TYPE)
				.append(" where t.order_pack_id=:orderPackId");
		PurOrderInfoForm form = (PurOrderInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("orderPackId", form.getOrderPackId());
		if (StringUtils.isNotBlank(form.getGoodsCode())) {
			sql.append(" and t.goods_code=:goodsCode");
			params.put("goodsCode", form.getGoodsCode());
		}
		if (StringUtils.isNotBlank(form.getBakDrugFlag())) {
			sql.append(" and t.bak_drug_flag=:bakDrugFlag");
			params.put("bakDrugFlag", form.getBakDrugFlag());
		}
		if (StringUtils.isNotBlank(form.getOrderStatus())) {
			sql.append(" and t.order_status=:orderStatus");
			params.put("orderStatus", form.getOrderStatus());
		}
		if (StringUtils.isNotBlank(form.getDrugCode())) {
			sql.append(" and c.drug_code=:drugCode");
			params.put("drugCode", form.getDrugCode());
		}
		if (StringUtils.isNotBlank(form.getDrugName())) {
			if (PinYin.isPinYin(form.getDrugName().trim())) {
				sql.append(" and c.drug_name_spel like :drugName");
				params.put("drugName", "%"
						+ form.getDrugName().trim().toUpperCase() + "%");
			} else {
				sql.append(" and c.drug_name like :drugName");
				params.put("drugName", "%" + form.getDrugName().trim() + "%");
			}
		}
		if (StringUtils.isNotBlank(form.getCompName())) {
			sql.append(" and c.comp_name like :compName");
			params.put("compName", "%" + form.getCompName().trim() + "%");
		}
		sql.append(" order by  t.join_plan_time desc ");
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}

	/**
	 * @category 已评价详情表单数据
	 * @Auther 徐国鹏
	 * @date 2015年8月4日 下午8:47:14
	 * @param interflowId
	 * @return PurOrderInfoForm
	 */
	@Override
	public PurOrderLogisticsOld getAoginfoById(String interflowId) {
		PurOrderLogisticsOld data = (PurOrderLogisticsOld) dao
				.getUniquerResult(PurOrderLogisticsOld.class, interflowId);
		return data;
	}

	 /**
	  * @category 保存信息到订单明细表
	  * @author 韩守松
	  * @date   2015年11月25日
	  * @param
	  */
	public void saveOrderDetail(Map<String, Object> map) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer hql = new StringBuffer();
		hql.append(" insert into pur_orderinfo(order_code,order_pack_id,pro_count,del_count,pro_price,join_plan_time,join_plan_user,");
		hql.append(" order_status,confirm_remark,goods_code,area_id,area_name,ar_shortname,ar_fullname,proj_id,proj_name,");
		hql.append(" proj_starttime,proj_endtime,proj_status,qualityspec_id,drug_code,qualityspec_name,qualityspec_name_spel,");
		hql.append(" qualityspec_name_eng,form_name,spec_name,qualityspec_status,bus_name,factor,unit_name,material_name,drug_medicine,");
		hql.append(" prod_compcode,prod_compname,prod_compname_spel,prod_compstatus,cat_comp_code,cat_comp_name,cat_comp_name_spel,");
		hql.append(" cat_comp_status,use_range,drug_attr,goods_source,pattern_type,is_healthcare,is_agriculture,is_lowprice,");
		hql.append(" is_smallprice,is_smallsource,is_longtime_plan,drug_status,drug_cat,pro_price_limit,retail_price_limit,hang_price,");
		hql.append(" ref_price,healthcare_price,pic_url,pic_short_url,status) ");
		hql.append(" values( ");
		hql.append(" :order_code , :order_pack_id , :pro_count , :del_count , :pro_price , :join_plan_time , :join_plan_user ,  ");
		hql.append(" :order_status , :confirm_remark , :goods_code , :area_id , :area_name , :ar_shortname , :ar_fullname , :proj_id ,");
		hql.append(" :proj_name , :proj_starttime , :proj_endtime , :proj_status , :qualityspec_id , :drug_code , :qualityspec_name , ");
		hql.append(" :qualityspec_name_spel , :qualityspec_name_eng , :form_name , :spec_name , :qualityspec_status , :bus_name , ");
		hql.append(" :factor , :unit_name , :material_name,:drug_medicine , :prod_compcode , :prod_compname , :prod_compname_spel , :prod_compstatus ,");
		hql.append(" :cat_comp_code , :cat_comp_name , :cat_comp_name_spel , :cat_comp_status , :use_range , :drug_attr ,");
		hql.append(" :goods_source , :pattern_type , :is_healthcare , :is_agriculture , :is_lowprice , :is_smallprice , :is_smallsource , ");
		hql.append(" :is_longtime_plan , :drug_status , :drug_cat , :pro_price_limit , :retail_price_limit , :hang_price , :ref_price ,");
		hql.append(" :healthcare_price , :pic_url , :pic_short_url , :status ");
		hql.append(" ) ");
		SQLQuery query = session.createSQLQuery(hql.toString());
		query.setString("order_code", map.get("order_code").toString());
		query.setString("order_pack_id", map.get("order_pack_id").toString());
		query.setInteger("pro_count",Integer.valueOf(map.get("pro_count").toString()));
		query.setInteger("del_count", Integer.valueOf(map.get("del_count").toString()));
		query.setDouble("pro_price", Double.valueOf(map.get("pro_price").toString()));
		query.setParameter("join_plan_time", map.get("join_plan_time"));
		query.setParameter("join_plan_user", map.get("join_plan_user"));
		query.setInteger("order_status", Integer.valueOf(map.get("order_status").toString()));
		query.setParameter("confirm_remark", map.get("confirm_remark").toString());
		query.setParameter("goods_code", map.get("goods_code").toString());
		query.setParameter("area_id", map.get("area_id"));
		query.setParameter("area_name", map.get("area_name"));
		query.setParameter("ar_shortname", map.get("ar_shortname"));
		query.setParameter("ar_fullname", map.get("ar_fullname"));
		query.setParameter("proj_id", map.get("proj_id").toString());
		query.setParameter("proj_name", map.get("proj_name").toString());
		query.setDate("proj_starttime", DateUtil.parseDate(map.get("proj_starttime").toString()));
		query.setDate("proj_endtime", DateUtil.parseDate(map.get("proj_endtime").toString()));
		query.setInteger("proj_status", Integer.valueOf(map.get("proj_status").toString()));
		query.setString("qualityspec_id", map.get("qualityspec_id")==null?"":map.get("qualityspec_id").toString());
		query.setString("drug_code", map.get("drug_code")==null?"":map.get("drug_code").toString());
		query.setString("qualityspec_name", map.get("qualityspec_name")==null?"":map.get("qualityspec_name").toString());
		query.setString("qualityspec_name_spel", map.get("qualityspec_name_spel")==null?"":map.get("qualityspec_name_spel").toString());
		query.setString("qualityspec_name_eng", map.get("qualityspec_name_eng")==null?"":map.get("qualityspec_name_eng").toString());
		query.setString("form_name", map.get("form_name").toString());
		query.setString("spec_name", map.get("spec_name").toString());
		query.setInteger("qualityspec_status", Integer.valueOf(map.get("qualityspec_status").toString()));
		query.setString("bus_name", map.get("bus_name")==null?"":map.get("bus_name").toString());
		query.setString("factor", map.get("factor")==null?"":map.get("factor").toString());
		query.setString("unit_name", map.get("unit_name")==null?"":map.get("unit_name").toString());
		query.setString("material_name", map.get("material_name")==null?"":map.get("material_name").toString());
		query.setString("drug_medicine", map.get("drug_medicine")==null?"":map.get("drug_medicine").toString());
		query.setString("prod_compcode", map.get("prod_compcode")==null?"":map.get("prod_compcode").toString());
		query.setString("prod_compname", map.get("prod_compname")==null?"":map.get("prod_compname").toString());
		query.setString("prod_compname_spel", map.get("prod_compname_spel")==null?"":map.get("prod_compname_spel").toString());
		query.setInteger("prod_compstatus", Integer.valueOf(map.get("prod_compstatus").toString()));
		query.setString("cat_comp_code", map.get("cat_comp_code")==null?"":map.get("cat_comp_code").toString());
		query.setString("cat_comp_name", map.get("cat_comp_name")==null?"":map.get("cat_comp_name").toString());
		query.setString("cat_comp_name_spel", map.get("cat_comp_name_spel")==null?"":map.get("cat_comp_name_spel").toString());
		query.setInteger("cat_comp_status", Integer.valueOf(map.get("cat_comp_status").toString()));
		query.setInteger("use_range", Integer.valueOf(map.get("use_range").toString()));
		query.setInteger("drug_attr", Integer.valueOf(map.get("drug_attr").toString()));
		query.setInteger("goods_source", Integer.valueOf(map.get("goods_source").toString()));
		query.setInteger("pattern_type", Integer.valueOf(map.get("pattern_type").toString()));
		query.setInteger("is_healthcare", Integer.valueOf(map.get("is_healthcare").toString()));
		query.setInteger("is_agriculture", Integer.valueOf(map.get("is_agriculture").toString()));
		query.setInteger("is_lowprice", Integer.valueOf(map.get("is_lowprice").toString()));
		query.setInteger("is_smallprice", Integer.valueOf(map.get("is_smallprice").toString()));
		query.setInteger("is_smallsource", Integer.valueOf(map.get("is_smallsource").toString()));
		if(null != map.get("is_longtime_plan")){
			query.setInteger("is_longtime_plan", Integer.valueOf(map.get("is_longtime_plan").toString()));
		}else{
			query.setBigInteger("is_longtime_plan", null);
		}
		query.setParameter("drug_status", map.get("drug_status"));
		query.setParameter("drug_cat", map.get("drug_cat"));
		query.setParameter("pro_price_limit", map.get("pro_price_limit"));
		query.setParameter("retail_price_limit", map.get("retail_price_limit"));
		query.setParameter("hang_price",map.get("hang_price")==null?"0.00":map.get("hang_price"));
		query.setParameter("ref_price", map.get("ref_price")==null?"0.00":map.get("ref_price"));
		query.setParameter("healthcare_price", map.get("healthcare_price")==null?"0.00":map.get("healthcare_price"));
		query.setParameter("pic_url",map.get("pic_url"));
		query.setParameter("pic_short_url", map.get("pic_short_url"));
		query.setParameter("status", map.get("status"));
		query.executeUpdate();
		session.flush();
		
		}
	
	/**
	  * @category 根据物流id查询
	  * @author wangbing
	  * @date   2015年11月25日
	  * @param
	  */
	@Override
	public List<?> getPurOrderlogisticsById(String id){
		StringBuffer sql = new StringBuffer()
		.append("select mid_datetime midDatetime,mid_desc midDesc,mid_user midUser from pur_orderlogistics where interflow_id = :id and mid_datetime is not null order by mid_datetime ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("id", id);
		return dao.getSqlMapList(sql.toString(), params);
	}
	
	/**
	  * @category 根据物流id查询各个时间点
	  * @author wangbing
	  * @date   2015年11月25日
	  * @param
	  */
	@Override
	public List<?> getTheTimesById(String id){
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer()
		.append("select * from pur_orderlogistics c ")
		.append(" where c.interflow_id =:id and c.recp_datetime is not null ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("id", id);
		return query.list();
	}
	
	/**
	  * @category 根据物流id查询各个订单商品
	  * @author wangbing
	  * @date   2015年11月25日
	  * @param
	  */
	@Override
	public List<?> getGoodInfoById(String id){
		StringBuffer sql = new StringBuffer()
		.append("select * from pur_orderinfo c ")
		.append(" where c.order_code in (select order_code from  pur_delorderinfo where interflow_id = :id) ");
		Map<String,Object> params = new HashMap<String,Object>();
		params.put("id", id);
		return dao.getSqlMapList(sql.toString(), params);
	}

	
	@Override
	public void auditHospOderInfo(Pagination page) {
		String state = page.getConditions().get("state");
		String param = page.getConditions().get("param");
		String[] strs = param.split(",");
		List<String> list = new ArrayList<String>();
		for(int i=0,temp=strs.length; i<temp ;i++){
			list.add(strs[i]);
		}
		Session session = getSession();
		session.createSQLQuery(" update pur_orderpack set order_status=:state,submit_datetime =:currentTime where order_pack_id in(:list)")
		.setParameter("state", state)
		.setParameter("currentTime", DateUtil.dateTime(new Timestamp(System.currentTimeMillis())))
		.setParameterList("list", list)
		.executeUpdate();
		session.flush();
		session.clear();
	}

	 /**
	  * 
	  *@category 医疗机构获取未审核的订单
	  *@author 应晓川
	  *@date 2015年12月4日10:52:34
	  *@param page
	  */
	@Override
	public Pagination getNotAuditHospOderList(Pagination page) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.remarks,a.order_pack_id orderPackId, a.hosp_code hospCode,   ");
		sb.append("  a.count, a.amount, a.is_urgent isUrgent, ");
		sb.append(" a.submit_datetime submitDatetime,a.del_comp_code delCompCode, ");
		sb.append(" a.recp_addr recpAddr, a.recp_userphone recpUserphone, a.recp_user recpUser,a.del_comp_name delCompName,a.order_status orderStatus ");
		sb.append(" FROM pur_orderpack a WHERE  ");
		sb.append("  a.order_status = :orderStatus  and a.hosp_code = :hospCode ");
		
		OrderPackage orderPackage = (OrderPackage) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("orderStatus", OrderPackStatus.submit.getKey());
		params.put("hospCode", orderPackage.getHospCode());
		
		if (StringUtils.isNotBlank(orderPackage.getOrderPackId())) {
			sb.append(" AND a.order_pack_id = :orderPackId ");
			params.put("orderPackId", orderPackage.getOrderPackId());
		}
		
		if (orderPackage.getStartDate() != null) {
			sb.append(" AND a.submit_datetime >= :startDate ");
			params.put("startDate", ParseDate.dateToDateTime(orderPackage.getStartDate()));
		}
		
		if (orderPackage.getEndDate() != null) {
			sb.append(" AND a.submit_datetime <= :endDate ");
			params.put("endDate", ParseDate.dateToEndDateTime(orderPackage.getEndDate()));
		}
		if(StringUtils.isNotBlank(orderPackage.getDelCompName())){
			sb.append(" and (a.del_comp_name like :delCompName or a.del_comp_name_spel like :delCompName)");
			params.put("delCompName", "%"+orderPackage.getDelCompName()+"%");
		}
		sb.append(" order by a.submit_datetime DESC ");
		return dao.getPageMapList(sb.toString(), params, page);
	
	}

	@Override
	public Object checkAuditHospOder(Pagination page) {
		String param = page.getConditions().get("param");
		String[] strs = param.split(",");
		List<String> list = new ArrayList<String>();
		for(int i=0,temp=strs.length; i<temp ;i++){
			list.add(strs[i]);
		}
		Session session = getSession();
		SQLQuery query = session.createSQLQuery(" select count(*) from pur_orderpack WHERE order_pack_id in (:list) and order_status=1 ");
		query.setParameterList("list", list);
		Object obj = query.uniqueResult();
		return obj;
	}

	
	@Override
	public Pagination getAuditHospOderResultList(Pagination page) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.remarks,a.order_pack_id orderPackId,a.order_status orderStatus, a.hosp_code hospCode,   ");
		sb.append("  a.count, a.amount, a.is_urgent isUrgent, ");
		sb.append(" a.submit_datetime submitDatetime,a.del_comp_code delCompCode, ");
		sb.append(" a.recp_addr recpAddr, a.recp_userphone recpUserphone, a.recp_user recpUser,a.del_comp_name delCompName ");
		sb.append(" FROM pur_orderpack a WHERE  ");
		sb.append(" a.hosp_code = :hospCode and (a.order_status = :orderStatus or a.order_status = :orderStatusF or  a.order_status = :orderStatusW ) ");
		
		OrderPackage orderPackage = (OrderPackage) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("orderStatus", OrderPackStatus.submit.getKey());
		params.put("orderStatusF", OrderPackStatus.pass.getKey());
		params.put("orderStatusW", OrderPackStatus.notPass.getKey());
		params.put("hospCode", orderPackage.getHospCode());
		
		if(orderPackage.getOrderStatus() != null){
			params.put("orderStatus", orderPackage.getOrderStatus());
			params.put("orderStatusF", orderPackage.getOrderStatus());
			params.put("orderStatusW", orderPackage.getOrderStatus());
		}
		if (StringUtils.isNotBlank(orderPackage.getOrderPackId())) {
			sb.append(" AND a.order_pack_id = :orderPackId ");
			params.put("orderPackId", orderPackage.getOrderPackId());
		}
		
		if (orderPackage.getStartDate() != null) {
			sb.append(" AND a.submit_datetime >= :startDate ");
			params.put("startDate", ParseDate.dateToDateTime(orderPackage.getStartDate()));
		}
		
		if (orderPackage.getEndDate() != null) {
			sb.append(" AND a.submit_datetime <= :endDate ");
			params.put("endDate", ParseDate.dateToEndDateTime(orderPackage.getEndDate()));
		}
		if(StringUtils.isNotBlank(orderPackage.getDelCompName())){
			sb.append(" and (a.del_comp_name like :delCompName or a.del_comp_name_spel like :delCompName)");
			params.put("delCompName", "%"+orderPackage.getDelCompName()+"%");
		}

		sb.append(" order by a.submit_datetime DESC ");
		return dao.getPageMapList(sb.toString(), params, page);
	
	}


	@SuppressWarnings("unchecked")
	@Override
	public  List<HashMap<String, Object>> getOrderPackAmountList(Pagination page) {
		 String[] param = page.getConditions().get("param").split(",");
		 if(param.length<0){
			 return null;
		 }
		 List<String> ids = new ArrayList<String>();
		 for(int i = 0,temp = param.length;i<temp;i++){
			 ids.add(param[i]);
		 }
		
		 String sql = "select a.goods_source goodsSource,a.pro_price proPrice,a.pro_count proCount FROM pur_orderinfo a WHERE a.order_pack_id in (:ids)";
		 Session session=  getSession();
		 List<HashMap<String, Object>> list = 
		 session.createSQLQuery(sql)
		 .setParameterList("ids", ids)
		 .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
		 .list();
		return list;
	}

}
